package com.kx.dao;

import com.kx.entity.Province;

import java.sql.*;

//使用jdbc访问数据库
public class ProvinceDao {
    //根据id获取名称
    public String queryProvinceNameById(Integer provinceId) {

        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql = "";
        String url = "jdbc:mysql://localhost:3306/ajaxjquery";

        String name = "";

        //加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, "root", "123456");

            sql = "select name from province where id=? ";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, provinceId);
            rs = pst.executeQuery();

//            while (rs.next()){
//                name=rs.getString("name");
//            }
            if (rs.next()) {
                name = rs.getString("name");
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return name;
    }
    //根据id获取Province对象
    public Province queryProvinceById(Integer provinceId) {

        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql = "";
        String url = "jdbc:mysql://localhost:3306/ajaxjquery";

        Province province=null;

        //加载驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, "root", "123456");

            sql = "select id,name,jiancheng,shenghui from province where id=? ";
            pst = conn.prepareStatement(sql);
            pst.setInt(1, provinceId);
            rs = pst.executeQuery();

//            while (rs.next()){
//                name=rs.getString("name");
//            }
            if (rs.next()) {
                province=new Province();
                province.setId(rs.getInt("id"));
                province.setName(rs.getString("name"));
                province.setJiancheng(rs.getString("jiancheng"));
                province.setShenghui(rs.getString("shenghui"));
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return province;
    }
}
